﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using com.dz.ht.common;

namespace com.dz.ht.db
{
    public class BookDao
    {
        /// <summary>
        /// 添加书的信息
        /// </summary>
        public long InsertBookInfo(BookInfo model,out string o_last_chapter_name)
        {
            o_last_chapter_name = string.Empty;
            if (!string.IsNullOrEmpty(model.BookUrl))
            {
                model.BookUrl = model.BookUrl.Replace("'", "").Replace("\"", "");
            }

            if (string.IsNullOrEmpty(model.BookName))
                return -1;

          
            if (!string.IsNullOrEmpty(model.BookCagegoryName))
            {
                model.BookCagegoryName = model.BookCagegoryName.Trim();
            }
            if (!string.IsNullOrEmpty(model.BookSubCagegoryName))
            {
                model.BookSubCagegoryName = model.BookSubCagegoryName.Trim();
            }

            model.BookName = model.BookName.Trim();
            try
            {
                int rowsAffected;
                SqlParameter[] parameters = {
					new SqlParameter("@bookId", SqlDbType.BigInt,8),
					new SqlParameter("@bookName", SqlDbType.NVarChar,100),
					new SqlParameter("@authorId", SqlDbType.BigInt,8),
					new SqlParameter("@authorName", SqlDbType.NVarChar,50),
					new SqlParameter("@isExistImage", SqlDbType.Bit,1),
					new SqlParameter("@description", SqlDbType.NVarChar,4000),
					new SqlParameter("@bookCategoryName", SqlDbType.NVarChar,50),
					new SqlParameter("@bookSubCategoryName", SqlDbType.VarChar,50),
					new SqlParameter("@collect", SqlDbType.BigInt,8),
					new SqlParameter("@click", SqlDbType.BigInt,8),
					new SqlParameter("@createTime", SqlDbType.DateTime),
					new SqlParameter("@bookStatus", SqlDbType.NVarChar,50),
					new SqlParameter("@wordsCount", SqlDbType.BigInt,8),
					new SqlParameter("@isVip", SqlDbType.Bit,1),
                    new SqlParameter("@siteId", SqlDbType.Int,4),
                    new SqlParameter("@bookUrl", SqlDbType.NVarChar,200),
                     new SqlParameter("@chapterUrl", SqlDbType.NVarChar,200),
                    new SqlParameter("@lastUpdateChapterName",SqlDbType.NVarChar,200)
                };
                parameters[0].Direction = ParameterDirection.Output;
                parameters[1].Value = model.BookName;
                parameters[2].Value = model.AuthorId;
                parameters[3].Value = model.AuthorName;
                parameters[4].Value = model.IsExistsImage;
                parameters[5].Value = model.Description;
                parameters[6].Value = model.BookCagegoryName;
                parameters[7].Value = model.BookSubCagegoryName;
                parameters[8].Value = model.Collect;
                parameters[9].Value = model.Click;
                parameters[10].Value = DateTime.Now;
                parameters[11].Value = model.BookStatus;
                parameters[12].Value = model.WordsCount;
                parameters[13].Value = model.IsVip;
                parameters[14].Value = model.SiteId;
                parameters[15].Value = model.BookUrl;
                parameters[16].Value = model.ChapterUrl;
                parameters[17].Direction = ParameterDirection.Output;
                DbHelperSQL.RunProcedure("proc_tbBook_Interface", parameters, out rowsAffected);
                o_last_chapter_name = parameters[17].Value.ToString();
                return (long)parameters[0].Value;
            }
            catch (Exception ex)
            {
                throw new DataException("添加书信息出现异常", ex);
            }
        }

        /// <summary>
        /// 添加章节信息
        /// </summary>
        /// <param name="i_volume_name"></param>
        /// <param name="i_chapter_name"></param>
        /// <param name="i_update_time"></param>
        /// <param name="i_is_vip"></param>
        /// <param name="i_words_count"></param>
        /// <param name="i_book_id"></param>
        /// <param name="i_site_id"></param>
        /// <returns></returns>
        public long InsertBookChapter(BookChapterInfo i_book_chapter_info,string i_last_chapter_name)
        {
            if (string.IsNullOrEmpty(i_book_chapter_info.ChapterName))
                return -1;
            i_book_chapter_info.ChapterName = i_book_chapter_info.ChapterName.Trim();
   
            i_book_chapter_info.ChapterName = System.Text.RegularExpressions.Regex.Replace(i_book_chapter_info.ChapterName, "[a-zA-Z\\.]?", "", System.Text.RegularExpressions.RegexOptions.IgnoreCase | System.Text.RegularExpressions.RegexOptions.Compiled);
            if (!string.IsNullOrEmpty(i_book_chapter_info.VolumeName))
            {
                i_book_chapter_info.VolumeName = i_book_chapter_info.VolumeName.Trim();
            }
            try
            {
                int rowsAffected;
                SqlParameter[] parameters = {
					new SqlParameter("@chapterId", SqlDbType.BigInt,8),
					new SqlParameter("@volumeName", SqlDbType.NVarChar,100),
					new SqlParameter("@updateTime", SqlDbType.DateTime),
					new SqlParameter("@isVip", SqlDbType.Bit,1),
                    new SqlParameter("@wordsCount", SqlDbType.Int,4),   
                    new SqlParameter("@bookId", SqlDbType.BigInt,8),
                    new SqlParameter("@chapterName", SqlDbType.NVarChar,200),
                    new SqlParameter("@siteId",SqlDbType.Int,4),
                    new SqlParameter("@comfrom",SqlDbType.NVarChar,200),
                    new SqlParameter("@lastChapterName",SqlDbType.NVarChar,200)

                };
                parameters[0].Direction = ParameterDirection.Output;
                parameters[1].Value = i_book_chapter_info.VolumeName;
                parameters[2].Value = i_book_chapter_info.UpdateTime;
                parameters[3].Value = i_book_chapter_info.IsVip;
                parameters[4].Value = i_book_chapter_info.WordsCount;
                parameters[5].Value = i_book_chapter_info.BookId; ;
                parameters[6].Value = i_book_chapter_info.ChapterName;
                parameters[7].Value = i_book_chapter_info.SiteId;
                parameters[8].Value = i_book_chapter_info.Comfrom;
                parameters[9].Value = i_last_chapter_name;
                DbHelperSQL.RunProcedure("proc_tbBookChapter_Interface", parameters, out rowsAffected);
                return (long)parameters[0].Value;
            }
            catch (Exception ex)
            {
                throw new DataException("添加章节信息失败", ex);
            }
        }

        /// <summary>
        /// 获取更新书籍
        /// </summary>
        /// <param name="i_site_id">站点id</param>
        /// <param name="i_type">-1全部书籍 0 上次抓取记录下的bookid开始</param>
        /// <param name="i_count">取多少条记录</param>
        /// <param name="i_order_type">1升序 0 降序</param>
        /// <returns></returns>
        public DataSet GetBook(Consts.Site i_site_id, int i_type, int i_count, int i_order_type)
        {
            try
            {

                SqlParameter[] parameters = {
					new SqlParameter("@siteId", SqlDbType.Int,4),
					new SqlParameter("@type", SqlDbType.Int,4),
					new SqlParameter("@count", SqlDbType.Int,4),
					new SqlParameter("@orderType", SqlDbType.Int,4)

                };

                parameters[0].Value = (int)i_site_id;
                parameters[1].Value = i_type;
                parameters[2].Value = i_count;
                parameters[3].Value = i_order_type;

                DataSet t_ds= DbHelperSQL.RunProcedure("proc_tbSiteBook_Get_All", parameters, "ds");

                return t_ds;
            }
            catch (Exception ex)
            {
                throw new DataException("获取需要更新小说失败", ex);
            }
        }

      
        /// <summary>
        /// 获取需要更新的章节
        /// </summary>
        /// <returns></returns>
        public DataSet GetChapter(int i_site_id)
        {
            try
            {

                SqlParameter[] parameters = {
					new SqlParameter("@siteId", SqlDbType.Int,4)

                };
                parameters[0].Value = i_site_id;
                DataSet t_ds = DbHelperSQL.RunProcedure("proc_tbBookChapterUpdate_Get", parameters, "ds");

                return t_ds;
            }
            catch (Exception ex)
            {
                throw new DataException("获取需要更新的章节失败", ex);
            }
        }

        /// <summary>
        /// 更新章节状态
        /// </summary>
        /// <param name="t_book_id"></param>
        /// <param name="t_chapter_id"></param>
        /// <returns></returns>
        public int UpdateChapterStatus(long t_book_id, long t_chapter_id)
        {
            int rowsAffected;
            try
            {
                SqlParameter[] parameters = {
                                                new SqlParameter("@bookId", SqlDbType.BigInt,8),
                                                new SqlParameter("@chapterId", SqlDbType.BigInt,8),
                                                  new SqlParameter("@status", SqlDbType.Int,4)
					};
                parameters[0].Value = t_book_id;
                parameters[1].Value = t_chapter_id;
                parameters[2].Value = 1;//未更新
                int t_ret = DbHelperSQL.RunProcedure("proc_tbBookChapterStatus_Update", parameters, out rowsAffected);
                return t_ret;
            }
            catch (Exception ex)
            {
                throw new DataException("更新章节状态失败!", ex);
            }
        }

        /// <summary>
        /// 插入书籍评论
        /// </summary>
        /// <param name="i_review_content"></param>
        /// <returns></returns>
        public long InsertBookReview(long i_book_id, string i_review_content)
        {
            int rowsAffected;
            long t_review_id = 0;
            try
            {
                SqlParameter[] parameters = {
                                                new SqlParameter("@reviewId", SqlDbType.BigInt,8),
                                                new SqlParameter("@bookId", SqlDbType.BigInt,8),
                                                new SqlParameter("@reviewContent", SqlDbType.VarChar,4000),
                                                new SqlParameter("@userId",SqlDbType.BigInt,8),
                                                new SqlParameter("@replyUserId",SqlDbType.BigInt,8)
					};
                parameters[0].Direction = ParameterDirection.Output;
                parameters[1].Value = i_book_id;
                parameters[2].Value = i_review_content;
                parameters[3].Value = 0;
                parameters[4].Value = 0;
                DbHelperSQL.RunProcedure("proc_tbReview_Interface", parameters, out rowsAffected);
                if (!long.TryParse(parameters[0].Value.ToString(), out t_review_id))
                    t_review_id = 0;
                return t_review_id;
            }
            catch (Exception ex)
            {
                throw new DataException("添加评论失败!", ex);
            }
        }

        /// <summary>
        /// 获取需要收集评论的书籍
        /// </summary>
        /// <param name="i_type">1 升序 0 降序 其他为bookid开始值</param>
        /// <returns></returns>
        public DataSet GetBookByReview(int i_type)
        {
            string t_str_sql = string.Empty;
            if (i_type == 1)
            {
                t_str_sql = "select bookId,bookName from tbBook a where not exists(select 1 from tbreview b where a.bookid=b.bookid) and  a.createtime>cast('2011-3-15 15:30:00' as datetime)  order by a.bookId ";
            }
            else if (i_type == 0)
            {
                t_str_sql = "select bookId,bookName from tbBook a where not exists(select 1 from tbreview b where a.bookid=b.bookid) order by a.bookId desc";
            }
            else
            {
                t_str_sql = string.Format("select bookId,bookName from tbBook a where not exists(select 1 from tbreview b where a.bookid=b.bookid) and bookId>{0} order by a.bookId", i_type);
            }

            try
            {
                return DbHelperSQL.Query(t_str_sql);
            }
            catch (Exception ex)
            {
                throw new DataException("获取需要收集评论的书籍失败", ex);
            }
        }

        /// <summary>
        /// 获取不存在封面的书籍
        /// </summary>
        /// <returns></returns>
        public DataSet GetBookICON()
        {
            string t_str_sql = "select bookId,bookName from tbBook a where isexistimage=0 ";
            try
            {
                return DbHelperSQL.Query(t_str_sql);
            }
            catch (Exception ex)
            {
                throw new DataException("获取没有封面的书籍失败", ex);
            }
        }

        /// <summary>
        /// 更新小说封面状态
        /// </summary>
        /// <param name="i_book_id"></param>
        /// <param name="i_exist_image"></param>
        /// <returns></returns>
        public int UpdateBookExistImage(long i_book_id, int i_exist_image)
        {

            try
            {
                SqlParameter[] parameters = {
                    new SqlParameter("@bookId", SqlDbType.BigInt,8),
				    new SqlParameter("@isExistsImage", SqlDbType.Int,4),
         
				  
                  };

                parameters[0].Value = i_book_id;
                parameters[1].Value = i_exist_image;

                int t_ret = 0;
                DbHelperSQL.RunProcedure("proc_UpdateBookExistsImage", parameters, out t_ret);
                return t_ret;
            }
            catch (Exception ex)
            {
                throw new DataException("判断书籍是否存在失败", ex);
            }

        }

       /// <summary>
       /// 更新小说收集章节配置
       /// </summary>
       /// <param name="i_site_info"></param>
       /// <returns></returns>
        public int UpdateBookSearchSite(SearchSiteInfo i_site_info)
        {

            try
            {
                SqlParameter[] parameters = {
                    new SqlParameter("@bookId", SqlDbType.BigInt,8),
				    new SqlParameter("@siteDomain", SqlDbType.NVarChar,200),
                    new SqlParameter("@chapterUrl", SqlDbType.NVarChar,200),
				    new SqlParameter("@siteEncoding", SqlDbType.NVarChar,200),
                    new SqlParameter("@forbitWords", SqlDbType.NVarChar,200),
                    new SqlParameter("@linkType", SqlDbType.Int,4),
                    new SqlParameter("@regChpaterList",  SqlDbType.NVarChar,200),
                    new SqlParameter("@regChpater", SqlDbType.NVarChar,200),
                    new SqlParameter("@result", SqlDbType.Int,4),
                  };

                parameters[0].Value = i_site_info.BookId; ;
                parameters[1].Value = i_site_info.SiteDomain;
                parameters[2].Value = i_site_info.ChapterUrl; ;
                parameters[3].Value = i_site_info.SiteEncoding;
                parameters[4].Value = i_site_info.ForbidWords; ;
                parameters[5].Value = i_site_info.LinkType;
                parameters[6].Value = i_site_info.RegChapterList;
                parameters[7].Value = i_site_info.RegChapter; ;
                parameters[8].Direction = ParameterDirection.Output;
                int t_ret = 0;
                DbHelperSQL.RunProcedure("proc_tbBookSite", parameters, out t_ret);
                if (!int.TryParse(parameters[8].Value.ToString(), out t_ret))
                    t_ret = -1;
                return t_ret;
            }
            catch (Exception ex)
            {
                throw new DataException("更新小说收集章节配置", ex);
            }

        }

        /// <summary>
        /// 查询小说
        /// </summary>
        /// <param name="i_str_sql"></param>
        /// <returns></returns>
        public DataSet QueryData(string i_str_sql)
        {
            try
            {
                return DbHelperSQL.Query(i_str_sql);
            }
            catch (Exception ex)
            {
                throw new DataException("获取小说列表失败", ex);
            }
        }


      /// <summary>
      /// 跟新小说收集错误信息
      /// </summary>
      /// <param name="i_book_id"></param>
      /// <param name="i_error_count"></param>
      /// <returns></returns>
        public int UpdateBookSearchError(long i_book_id,int i_error_type)
        {

            try
            {
                SqlParameter[] parameters = {
                    new SqlParameter("@bookId", SqlDbType.BigInt,8),
				    new SqlParameter("@errorType", SqlDbType.Int,4)
                  };

                parameters[0].Value = i_book_id;
                parameters[1].Value = i_error_type;
               
                int t_ret = 0;
                DbHelperSQL.RunProcedure("proc_tbBook_Search_Error", parameters, out t_ret);
            
                return t_ret;
            }
            catch (Exception ex)
            {
                throw new DataException("更新小说收集章节配置", ex);
            }

        }

        /// <summary>
        /// 更新小说章节更新地址
        /// </summary>
        /// <param name="i_site_id"></param>
        /// <param name="i_book_name"></param>
        /// <param name="i_chapter_url"></param>
        /// <returns></returns>
        public long UpdateBookChapterUrl(int i_site_id,string i_book_name,string i_chapter_url)
        {

            try
            {
                SqlParameter[] parameters = {
                    new SqlParameter("@bookId", SqlDbType.BigInt,8),
				    new SqlParameter("@bookName", SqlDbType.NVarChar,200),
                    new SqlParameter("@chapterUrl", SqlDbType.NVarChar,200),
				    new SqlParameter("@siteId", SqlDbType.Int,4)
                  };

                parameters[0].Direction = ParameterDirection.Output;
                parameters[1].Value = i_book_name;
                parameters[2].Value = i_chapter_url;
                parameters[3].Value = i_site_id;
               
                long t_book_id = 0;
                int t_ret = 0;
                DbHelperSQL.RunProcedure("proc_tbBookSite_Collect", parameters, out t_ret);
                if (!long.TryParse(parameters[0].Value.ToString(), out t_book_id))
                    t_book_id = -1;
                return t_book_id;
            }
            catch (Exception ex)
            {
                throw new DataException("更新小说章节更新地址", ex);
            }

        }

       /// <summary>
       /// 添加小说收集信息
       /// </summary>
       /// <param name="i_book_id"></param>
       /// <param name="i_chapter_url"></param>
       /// <param name="i_site_id"></param>
       /// <returns></returns>
        public int AddBookSearch(string i_book_name,string i_chapter_url, int i_site_id)
        {

            try
            {
                SqlParameter[] parameters = {
                    new SqlParameter("@bookName", SqlDbType.NVarChar,100),
				    new SqlParameter("@chapterUrl", SqlDbType.NVarChar,200),
                      new SqlParameter("@siteId", SqlDbType.Int,4),
                       new SqlParameter("@result", SqlDbType.Int,4)
                  };

                parameters[0].Value = i_book_name;
                parameters[1].Value = i_chapter_url;
                parameters[2].Value = i_site_id;
                parameters[3].Direction = ParameterDirection.Output;

                int t_ret = 0;
                DbHelperSQL.RunProcedure("proc_tbBook_Search_Add", parameters, out t_ret);
                if (!int.TryParse(parameters[3].Value.ToString(), out t_ret))
                    t_ret = 0;
                return t_ret;
            }
            catch (Exception ex)
            {
                throw new DataException("更新小说收集章节配置", ex);
            }

        }
    }
}
